package com.elitel.common.businessJdbc;

import com.elitel.common.access.FilterStationDataSqlHandler;
import com.elitel.common.exception.BusinessStatementException;
import com.elitel.common.utils.ScriptSqlFieldTypeHelper;
import com.elitel.common.utils.XCommonUtils;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

// here is handle script preparedStatement ,iterate placeHolderColumnObject, set type (type is java type )
@Data
@Component
@Slf4j
public class BusinessScriptPreparedStatementHandler extends BusinessPreparedStatementHandler{

    @Autowired
    private ScriptSqlFieldTypeHelper scriptSqlFieldTypeHelper;

    // here is pass sql , here is not existed where condition
    // maybe pass field section , table section, where condition (BusinessPrepareStatementCondition)
    // here concat sql , and concat prepareStatement sql by parse BusinessPrepareStatementCondition
    // success using general rule
    public int rowCountByExecuteSql(BusinessScriptPreparedStatement businessPrepareStatement){
        Connection connection = null;
        PreparedStatement prepareStatement = null;
        ResultSet rs = null;

        int count = 0;

        String precompileCountSql = FilterStationDataSqlHandler.doFilterSql(businessPrepareStatement.getPrecompileCountSql()
                ,businessPrepareStatement.getFilterSqlHandlerDTO()
                ,businessPrepareStatement.getDatabaseType());
        SqlRecorder recorder = new SqlRecorder();
        recorder.setServiceGuid(businessPrepareStatement.getServiceGuid());
        recorder.setPrecompileSql(precompileCountSql);
        try{
            // here is get connection
            connection = getConnection();
            // connection get preparestatement
            prepareStatement = connection.prepareStatement(precompileCountSql);

            handlPrecompileParameter(recorder, businessPrepareStatement, prepareStatement);

            if(recorder.canExecuteSql()){
                log.info(recorder.formatInfo());
            }else{
                log.error(recorder.formatInfo());
            }

            // preparestatement.execute
            rs = prepareStatement.executeQuery();
            if(rs.next()){
                count = rs.getInt(1);
            }
            // close resource
            rs.close();
            prepareStatement.close();
            connection.close();

        }catch (Exception e){
            log.error(e.getMessage());
            try{
                if(rs != null) rs.close();
                if(prepareStatement != null) prepareStatement.close();
                if(connection != null) connection.close();
            }catch(SQLException sqlE){
                log.error(sqlE.getMessage());
            }

            throw new BusinessStatementException(e.getMessage(), recorder.formatInfo());
        }

        return count;
    }

    public List<Map<String, Object>> findRowListOfFieldResultByExecuteSql(BusinessScriptPreparedStatement businessPrepareStatement){

        List<Map<String, Object>> fieldResultRowList = new LinkedList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;

        String precompileSql = FilterStationDataSqlHandler.doFilterSql(businessPrepareStatement.getPrecompileSql()
                , businessPrepareStatement.getFilterSqlHandlerDTO()
                , businessPrepareStatement.getDatabaseType());
        SqlRecorder recorder = new SqlRecorder();
        recorder.setServiceGuid(businessPrepareStatement.getServiceGuid());
        recorder.setPrecompileSql(precompileSql);

        try{
            // here is get connection
            connection = getConnection();
            // connection get preparestatement
            preparedStatement = connection.prepareStatement(precompileSql);

            handlPrecompileParameter(recorder, businessPrepareStatement, preparedStatement);

            if(recorder.canExecuteSql()){
                log.info(recorder.formatInfo());
            }else{
                log.error(recorder.formatInfo());
            }

            // preparestatement.execute
            rs = preparedStatement.executeQuery();
            // here rs existed value
            ResultSetMetaData metaData = rs.getMetaData();
            int columnLength = metaData.getColumnCount();
            while(rs.next()){
                Map<String, Object> fieldResultRow = new HashMap<>(columnLength);
                for(int i=0; i<columnLength; i++){
                    String columnName = metaData.getColumnName(i + 1);
                    Object columnValue = rs.getObject(columnName);
                    if(null == columnValue){
                        fieldResultRow.put(columnName, columnValue);
                        continue;
                    }
                        // columnValue = "";
                    //处理 oracle.sql.TIMESTAMP报错
                    if (columnValue.getClass().getName().equals("oracle.sql.TIMESTAMP")){
                        Class clz = columnValue.getClass();
                        Method m = clz.getMethod("timestampValue");
                        java.sql.Timestamp t1 = (java.sql.Timestamp) m.invoke(columnValue);
                        fieldResultRow.put(columnName, t1);
                    } else {
                        fieldResultRow.put(columnName, columnValue);
                    }

                }
                fieldResultRowList.add(fieldResultRow);
            }
        } catch (Exception e) {
            log.error("获取数据库资源异常", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ex) {
                log.error("查询到数据记录fieldResultRowList.size=" + fieldResultRowList.size() + "，释放数据库资源异常", ex);
            }
        }
        return fieldResultRowList;
    }

    private void handlPrecompileParameter(SqlRecorder recorder, BusinessScriptPreparedStatement businessPrepareStatement, PreparedStatement prepareStatement){
        // handle sql , concat sql
        // here is forEach to handle
        int parameterIndex = 1;
        for(Map.Entry<String, Object> placeHolderColumnEntry : businessPrepareStatement.getPlaceHolderOrderColumnObject().entrySet()){
            Map<String,Object> columnMap = (Map<String, Object>) placeHolderColumnEntry.getValue();
            String columnName = String.valueOf(columnMap.get("key"));
            Object columnObject = columnMap.get("val");
            String columnValue;
            if(columnObject != null){
                columnValue = columnObject.toString().trim();
            }else{
                columnValue = "";
            }
            // here is get type to match method
            String fieldJavaType = businessPrepareStatement.getFieldTypeMap().get(columnName);
            if(XCommonUtils.isEmpty(fieldJavaType)){
                fieldJavaType = scriptSqlFieldTypeHelper.getSqlFieldTypeFromJsonFile(columnName);
            }
            if (XCommonUtils.isEmpty(fieldJavaType)) {
                recorder.addErrorMessage("字段" + columnName + "的Java类型不能为空, 请配置");
            }
            String parameterInfo = "index = " + parameterIndex + " contionItem: " + columnName + " = " + columnValue + " fieldType = " + (fieldJavaType == null ?  " (default String)" : fieldJavaType);

            try{
                // here is by fieldType ,it's java type
                if(matchPreparedStatementSetMethodByJavaType(prepareStatement, parameterIndex++, columnValue, fieldJavaType)){
                    recorder.addParameterInformation(parameterInfo);
                }else{
                    recorder.addParameterInformation("FAIL: " + parameterInfo);
                    recorder.addErrorMessage("FAIL: matchPreparedStatementSetMethodByJavaType match false, because of " + parameterInfo);
                }
            }catch(Exception e){
                recorder.addParameterInformation("FAIL: " + parameterInfo);
                recorder.addErrorMessage(e.getMessage());
            }
        }
    }

    private boolean matchPreparedStatementSetMethodByJavaType(PreparedStatement ps, int parameterIndex, String columnValue, String fieldJavaType) throws Exception{
        if(XCommonUtils.isEmpty(fieldJavaType))
            return false;

        if("Byte".equals(fieldJavaType)){
            ps.setInt(parameterIndex, Integer.parseInt(columnValue));
        }else if ("String".equals(fieldJavaType)){
            ps.setString(parameterIndex, columnValue);
        }else if ("BigDecimal".equals(fieldJavaType)){
            ps.setBigDecimal(parameterIndex, new BigDecimal(columnValue));
        }else if ("Short".equals(fieldJavaType)){
            ps.setShort(parameterIndex, (short)Integer.parseInt(columnValue));
        }else if ("Integer".equals(fieldJavaType)){
            ps.setInt(parameterIndex, Integer.parseInt(columnValue));
        }else if ("Long".equals(fieldJavaType)){
            ps.setLong(parameterIndex, Long.parseLong(columnValue));
        }else if ("Float".equals(fieldJavaType)){
            ps.setFloat(parameterIndex, Float.parseFloat(columnValue));
        }else if ("Double".equals(fieldJavaType)){
            ps.setDouble(parameterIndex, Double.parseDouble(columnValue));
        }else if ("byte[]".equals(fieldJavaType)){
            ps.setBytes(parameterIndex, columnValue.getBytes());
        }else if ("Date".equals(fieldJavaType)){
            Date date = ChineseDateFormat.parse(columnValue);
            ps.setDate(parameterIndex, new java.sql.Date(date.getTime()));
        }else if ("Time".equals(fieldJavaType)){
            Date date = ChineseDateFormat.parse(columnValue);
            ps.setTime(parameterIndex, new java.sql.Time(date.getTime()));
        }else if ("Timestamp".equals(fieldJavaType)){
            Date date = ChineseDateFormat.parse(columnValue);
            ps.setTimestamp(parameterIndex, new java.sql.Timestamp(date.getTime()));
        }else if ("Boolean".equals(fieldJavaType)){
            ps.setBoolean(parameterIndex, Boolean.parseBoolean(columnValue));
        }else{
            ps.setString(parameterIndex, columnValue);
            return false;
        }
        return true;
    }

    public static final SimpleDateFormat ChineseDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:ss:mm");


}
